// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

namespace MsSqlSpatial
{
	/// <summary>
	/// </summary>
	public static class GeometryTable
	{

		internal static void ExecuteCreate(SqlCommand command, string schemaName, string tableName, string columnName, string createColumns)
		{
			DatabaseHelper.BuildCommand(command, string.Format(
@"CREATE TABLE [{0}].[{1}] (
oid bigint IDENTITY PRIMARY KEY
,[{2}] varbinary(max)
{3})"
, schemaName, tableName, columnName, createColumns));
			command.ExecuteNonQuery();
		}

		internal static void ExecuteDropTableDependantObjects(SqlCommand command, string schemaName, string tableName)
		{
			if (DatabaseHelper.CheckMetaTableExists(command, "GEOMETRY_COLUMNS"))
			{
				foreach (string columnName in GeometryColumnsTable.GetGeometryColumnsByTable(command, schemaName, tableName))
				{
					StoredProcedures.ExecuteDropGeometryQueryFunctions(command, schemaName, tableName, columnName);
				}
				GeometryColumnsTable.ExecuteDeleteMetaRecords(command, schemaName, tableName);
			}
		}

		internal static void ExecuteDropColumnDependantObjects(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			StoredProcedures.ExecuteDropGeometryQueryFunctions(command, schemaName, tableName, columnName);
			GeometryTable.ExecuteDropTrigger(command, schemaName, tableName, columnName);
			GeometryTable.ExecuteDropConstraintGeometryType(command, schemaName, tableName, columnName);
			GeometryTable.ExecuteDropConstraintSRID(command, schemaName, tableName, columnName);
			GeometryTable.ExecuteDropIndexes(command, schemaName, tableName, columnName);
			GeometryColumnsTable.ExecuteDeleteMetaRecord(command, schemaName, tableName, columnName);
		}

		internal static void ExecuteAddColumn(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			DatabaseHelper.BuildCommand(command, string.Format(
@"ALTER TABLE [{0}].[{1}] 
ADD [{2}] varbinary(max)
"
, schemaName, tableName, columnName));
			command.ExecuteNonQuery();
		}

		public static void ExecuteAddConstraintGeometryType(SqlCommand command, string schemaName, string tableName, string columnName, string type)
		{
			if (!string.IsNullOrEmpty(type) && type != "GEOMETRY")
			{
				int oldTimeout = command.CommandTimeout;
				command.CommandTimeout = 0;

				DatabaseHelper.BuildCommand(command, string.Format(
@"ALTER TABLE [{0}].[{1}]
ADD CONSTRAINT [enforce_type_{1}_{2}] 
CHECK ([ST].[GeometryType]([{2}]) = '{3}' OR [{2}] IS NULL)"
, schemaName, tableName, columnName, type));
				command.ExecuteNonQuery();

				command.CommandTimeout = oldTimeout;
			}
		}
	
		public static void ExecuteDropConstraintGeometryType(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			string constraintName = string.Format("enforce_type_{0}_{1}", tableName, columnName);
			if (DatabaseHelper.CheckConstraintExists(command, schemaName, tableName, constraintName))
			{
				DatabaseHelper.BuildCommand(command, string.Format(
@"ALTER TABLE [{0}].[{1}]
DROP CONSTRAINT [{2}]"
, schemaName, tableName, constraintName));
				command.ExecuteNonQuery();
			}
		}
	
		public static void ExecuteAddConstraintSRID(SqlCommand command, string schemaName, string tableName, string columnName, int srid)
		{
			int oldTimeout = command.CommandTimeout;
			command.CommandTimeout = 0;

			DatabaseHelper.BuildCommand(command, string.Format(
@"ALTER TABLE [{0}].[{1}]
ADD CONSTRAINT [enforce_srid_{1}_{2}] 
CHECK ([{2}] IS NULL OR [ST].[SRID]([{2}]) = {3})"
, schemaName, tableName, columnName, srid));
			command.ExecuteNonQuery();
			command.CommandTimeout = oldTimeout;
		}

		public static void ExecuteDropConstraintSRID(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			string constraintName = string.Format("enforce_srid_{0}_{1}", tableName, columnName);
			if (DatabaseHelper.CheckConstraintExists(command, schemaName, tableName, constraintName))
			{
				DatabaseHelper.BuildCommand(command, string.Format(
@"ALTER TABLE [{0}].[{1}]
DROP CONSTRAINT [{2}]"
, schemaName, tableName, constraintName));
				command.ExecuteNonQuery();
			}
		}

		public static void ExecuteAddIndexes(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			int oldTimeout = command.CommandTimeout;
			command.CommandTimeout = 0;

			DatabaseHelper.BuildCommand(command, string.Format(
@"ALTER TABLE [{0}].[{1}] 
ADD 
 [{2}_Envelope_MinX] float
,[{2}_Envelope_MinY] float
,[{2}_Envelope_MaxX] float
,[{2}_Envelope_MaxY] float
"
, schemaName, tableName, columnName));
			command.ExecuteNonQuery();

			DatabaseHelper.BuildCommand(command, string.Format(
@"UPDATE [{0}].[{1}] SET
 [{2}_Envelope_MinX] = ST.MinX([{2}])
,[{2}_Envelope_MinY] = ST.MinY([{2}])
,[{2}_Envelope_MaxX] = ST.MaxX([{2}])
,[{2}_Envelope_MaxY] = ST.MaxY([{2}])
"
, schemaName, tableName, columnName));
			command.ExecuteNonQuery();

			DatabaseHelper.BuildCommand(command, string.Format(
@"CREATE INDEX [IDX_{2}_Envelope_MinX] ON [{0}].[{1}] ([{2}_Envelope_MinX])"
, schemaName, tableName, columnName));
			command.ExecuteNonQuery();

			DatabaseHelper.BuildCommand(command, string.Format(
@"CREATE INDEX [IDX_{2}_Envelope_MinY] ON [{0}].[{1}] ([{2}_Envelope_MinY])"
, schemaName, tableName, columnName));
			command.ExecuteNonQuery();

			DatabaseHelper.BuildCommand(command, string.Format(
@"CREATE INDEX [IDX_{2}_Envelope_MaxX] ON [{0}].[{1}] ([{2}_Envelope_MaxX])"
, schemaName, tableName, columnName));
			command.ExecuteNonQuery();

			DatabaseHelper.BuildCommand(command, string.Format(
@"CREATE INDEX [IDX_{2}_Envelope_MaxY] ON [{0}].[{1}] ([{2}_Envelope_MaxY])"
, schemaName, tableName, columnName));
			command.ExecuteNonQuery(); 
			
			DatabaseHelper.BuildCommand(command, string.Format(
@"CREATE INDEX [IDX_{2}_Envelope] ON [{0}].[{1}] (
 [{2}_Envelope_MinX]
,[{2}_Envelope_MaxX]
,[{2}_Envelope_MinY]
,[{2}_Envelope_MaxY]
)"
, schemaName, tableName, columnName));
			command.ExecuteNonQuery();

			command.CommandTimeout = oldTimeout;
		}
	
		public static void ExecuteDropIndexes(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			DatabaseHelper.DropIndex(command, schemaName, tableName, "IDX_" + columnName + "_Envelope_MinX");
			DatabaseHelper.DropIndex(command, schemaName, tableName, "IDX_" + columnName + "_Envelope_MinY");
			DatabaseHelper.DropIndex(command, schemaName, tableName, "IDX_" + columnName + "_Envelope_MaxX");
			DatabaseHelper.DropIndex(command, schemaName, tableName, "IDX_" + columnName + "_Envelope_MaxY");
			DatabaseHelper.DropIndex(command, schemaName, tableName, "IDX_" + columnName + "_Envelope");

			DatabaseHelper.DropColumn(command, schemaName, tableName, columnName + "_Envelope_MinX");
			DatabaseHelper.DropColumn(command, schemaName, tableName, columnName + "_Envelope_MinY");
			DatabaseHelper.DropColumn(command, schemaName, tableName, columnName + "_Envelope_MaxX");
			DatabaseHelper.DropColumn(command, schemaName, tableName, columnName + "_Envelope_MaxY");
		}

		private static string BuildTriggerName(string tableName, string columnName)
		{
			return "ST_GeometryTableTrigger_" + tableName + "_" + columnName;
		}

		public static void ExecuteCreateTrigger(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			string triggerName = BuildTriggerName(tableName, columnName);

			if (!DatabaseHelper.CheckTriggerExists(command, schemaName, triggerName))
			{
				string primaryKey = GeometryTable.GetPrimaryKeyColumn(command, schemaName, tableName);
				DatabaseHelper.BuildCommand(command, string.Format(
@"CREATE TRIGGER [{0}].[{3}] 
ON [{0}].[{1}]
WITH EXECUTE AS CALLER 
AFTER UPDATE, INSERT 
AS
BEGIN
	IF UPDATE({2})
	BEGIN
		SET NOCOUNT ON

		UPDATE [{0}].[{1}] SET
		 [{2}_Envelope_MinX] = ST.MinX(inserted.[{2}])
		,[{2}_Envelope_MinY] = ST.MinY(inserted.[{2}])
		,[{2}_Envelope_MaxX] = ST.MaxX(inserted.[{2}])
		,[{2}_Envelope_MaxY] = ST.MaxY(inserted.[{2}])
		FROM inserted
		INNER JOIN [{0}].[{1}]
		ON inserted.[{4}] = [{0}].[{1}].[{4}]

		SET NOCOUNT OFF
	END
END"
, schemaName
, tableName
, columnName
, triggerName
, primaryKey
));
				command.ExecuteNonQuery();
			}
		}

		public static void ExecuteDropTrigger(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			string triggerName = BuildTriggerName(tableName, columnName);
			if (DatabaseHelper.CheckTriggerExists(command, schemaName, triggerName))
			{
				DatabaseHelper.BuildCommand(command, string.Format(
@"DROP TRIGGER [{0}].[{1}]"
, schemaName, triggerName));
				command.ExecuteNonQuery();
			}
		}

		internal static string GetPrimaryKeyColumn(SqlCommand command, string schemaName, string tableName)
		{
			// Get primary key column name from table metadata
			DatabaseHelper.BuildCommand(command,
@"EXECUTE sp_pkeys @p1, @p0"
, schemaName, tableName);
			string primaryKey = null;
			using (SqlDataReader reader = command.ExecuteReader())
			{
				if (reader.Read())
				{
					primaryKey = reader.GetString(reader.GetOrdinal("COLUMN_NAME"));
				}
			}

			// If doesn't have primary key, add it
			if (string.IsNullOrEmpty(primaryKey))
			{
				primaryKey = tableName + "_oid";
				DatabaseHelper.BuildCommand(command, string.Format(
@"ALTER TABLE [{0}].[{1}]
ADD [{2}] bigint IDENTITY PRIMARY KEY"
, schemaName, tableName, primaryKey));
				command.ExecuteNonQuery();
			}

			return primaryKey;
		}

	}
}
